<?php

namespace app\admin\service\common;

use app\common\facade\Principal;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use think\exception\ValidateException;
use think\facade\Db;
use think\facade\Validate;

/**
 * 重构后的高性能、配置驱动的Excel导入服务
 */
class ImportService
{
    /** @var int 分块读取的行数。可根据服务器内存调整，1000是一个比较均衡的值。 */
    private $chunkSize = 1000;

    /**
     * 执行导入主方法
     * @param string $tempPath 上传的Excel临时文件路径
     * @param int $menuId 菜单ID，用于获取字段配置
     * @param string $table 目标数据表名
     * @param array $options 导入选项，如 ['mode' => 'covering']
     * @param array $userInfo 当前操作用户信息
     * @return array 包含成功、失败信息的关联数组
     */
    public function importExcel($tempPath, $menuId, $table, $options): array
    {
        $userInfo = Principal::getUserInfo();
        // --- 1. 准备阶段：获取配置、初始化变量 ---
        $fieldConfig = $this->getFieldConfig($menuId);
        if (empty($fieldConfig)) {
            throw new ValidateException('未找到该功能的字段配置信息，请检查后台配置');
        }
        $titleToField = array_column($fieldConfig, 'field', 'title');
        $fieldToConfig = array_column($fieldConfig, null, 'field');

        $reader = IOFactory::createReaderForFile($tempPath);
        $spreadsheet = $reader->load($tempPath);
        $worksheet = $spreadsheet->getActiveSheet();
        $totalRows = $worksheet->getHighestRow();
        $headerRow = $worksheet->rangeToArray('A1:' . $worksheet->getHighestColumn() . '1', null, true, false)[0];

        // --- 2. 解析表头，建立Excel列索引到数据库字段的映射 ---
        $columnMap = $this->buildColumnMap($headerRow, $titleToField, $fieldToConfig);

        // --- 3. 预加载数据以优化UNIQUE检查 ---
        $uniqueChecks = $this->prepareUniqueChecks($fieldToConfig, $table);

        // --- 4. 根据导入模式处理现有数据 (使用更安全的方式) ---
        Db::startTrans();
        try {
            if ($options['mode'] === 'covering') {
                Db::name($table)->delete(true);
            }

            $successCount = 0;
            $errorRows = [];

            // --- 5. 分块读取和处理数据 ---
            for ($startRow = 2; $startRow <= $totalRows; $startRow += $this->chunkSize) {
                $chunkData = $this->readChunk($reader, $tempPath, $startRow, $this->chunkSize);

                $insertData = [];
                foreach ($chunkData as $rowIndex => $row) {
                    try {
                        // 处理单行数据
                        $rowData = $this->processRow($row, $columnMap, $uniqueChecks, $table);

                        // 补充通用字段
                        $rowData['create_time'] = date('Y-m-d H:i:s');
                        $rowData['create_id'] = $userInfo['id'];
                        $rowData['create_dept'] = $userInfo['deptId'];

                        $insertData[] = $rowData;

                    } catch (\Exception $e) {
                        $errorRows[] = ['row' => $rowIndex, 'error' => $e->getMessage()];
                    }
                }

                // 批量插入当前块的数据
                if (!empty($insertData)) {
                    Db::name($table)->insertAll($insertData);
                    $successCount += count($insertData);
                }
            }

            // 导入后统一更新排序字段（如果需要）
            // 注意：此操作可能在数据量大时非常耗时，需要评估
            // Db::query("UPDATE {$table} SET sort = id");

            Db::commit();

            return [
                'success' => true,
                'message' => "导入完成！成功 {$successCount} 条，失败 " . count($errorRows) . " 条。",
                'success_count' => $successCount,
                'error_count' => count($errorRows),
                'errors' => $errorRows,
            ];

        } catch (\Exception $e) {
            Db::rollback();
            // 抛出更具体的异常信息
            throw new ValidateException("导入失败，事务已回滚： " . $e->getMessage());
        }
    }


    /**
     * 通用文件验证函数
     * @param \think\File $file 文件对象
     * @param int $maxSize 最大文件大小（字节）
     * @param string $exts 允许的文件扩展名，逗号分隔
     * @param array $mimes 允许的MIME类型数组
     * @return bool
     */
    public function validateFile($file): bool
    {
        if (!$file) {
            return false;
        }

        $upload_max_size = (int)config('rds.upload_max_size');
        $maxSize = 1024 * 1024 * $upload_max_size;
        $exts = 'xlsx,xls';

        $rules = [
            'file' => [
                'fileSize' => $maxSize,
                'fileExt' => $exts
            ]
        ];

        $mimes = config('rds.upload_file_mime');
        if (!empty($mimes)) {
            $rules['file']['fileMime'] = implode(',', $mimes);
        }

        $validate = Validate::rule($rules);

        $result = $validate->check(['file' => $file]);

        if (!$result) {
            return false;
        }

        return true;
    }

    /**
     * 读取Excel的一个数据块
     * @param \PhpOffice\PhpSpreadsheet\Reader\IReader $reader
     * @param string $filePath
     * @param int $startRow
     * @param int $chunkSize
     * @return array
     */
    private function readChunk($reader, $filePath, $startRow, $chunkSize): array
    {
        $filter = new class($startRow, $chunkSize) implements IReadFilter {
            private $startRow;
            private $endRow;
            public function __construct($startRow, $chunkSize) {
                $this->startRow = $startRow;
                $this->endRow = $startRow + $chunkSize - 1;
            }
            public function readCell($column, $row, $worksheetName = ''): bool {
                return $row >= $this->startRow && $row <= $this->endRow;
            }
        };
        $reader->setReadFilter($filter);
        $spreadsheet = $reader->load($filePath);
        $data = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
        $spreadsheet->disconnectWorksheets(); // 释放内存
        unset($spreadsheet);
        return $data;
    }

    /**
     * 处理单行数据：转换、验证
     * @param array $row Excel中的一行数据
     * @param array $columnMap 列映射
     * @param array &$uniqueChecks (引用传递) 用于唯一性检查的缓存
     * @param string $table 表名
     * @return array 处理后的行数据
     * @throws \Exception
     */
    private function processRow($row, $columnMap, &$uniqueChecks, $table): array
    {
        // 过滤完全空行
        if (empty(array_filter($row, fn($val) => !is_null($val) && $val !== ''))) {
            throw new \Exception("跳过空行"); // 使用异常来跳过，以便在主循环中捕获
        }

        $rowData = [];
        foreach ($columnMap as $colIndex => $map) {
            $value = $row[$colIndex] ?? null;
            $field = $map['field'];
            $config = $map['config'];

            // 核心：字段验证与转换
            $processedValue = $this->validateField($value, $field, $config, $uniqueChecks, $table);
            $rowData[$field] = $processedValue;
        }
        return $rowData;
    }

    /**
     * 验证单个字段值 (重构版)
     * @param mixed $value 单元格原始值
     * @param string $field 数据库字段名
     * @param array $config 字段配置
     * @param array &$uniqueChecks (引用传递) 唯一性检查缓存
     * @param string $table 表名
     * @return mixed 处理后的值
     * @throws \Exception
     */
    private function validateField($value, $field, $config, &$uniqueChecks, $table)
    {
        $fieldType = $config['field_type'] ?? 'varchar';
        $constraints = $config['field_constraint'] ?? [];
        $maxLength = $config['field_length'] ?? null;

        // 1. 空值处理
        if ($value === null || $value === '') {
            if (in_array('NOT NULL', $constraints)) {
                throw new \Exception("字段 [{$field}] 不允许为空");
            }
            return $config['field_default'] ?? null;
        }

        // 2. 类型校验与转换
        switch ($fieldType) {
            case 'bigint':
            case 'int':
                if (!is_numeric($value)) throw new \Exception("字段 [{$field}] 的值 '{$value}' 必须为整数");
                $value = (int)$value;
                break;
            case 'varchar':
            case 'text':
                $value = (string)$value;
                if ($maxLength && mb_strlen($value) > $maxLength) {
                    throw new \Exception("字段 [{$field}] 的内容超出最大长度 {$maxLength}");
                }
                break;
            case 'datetime':
                try {
                    if (is_numeric($value) && $value > 1) {
                        $value = Date::excelToDateTimeObject($value)->format('Y-m-d H:i:s');
                    } else {
                        $value = (new \DateTime($value))->format('Y-m-d H:i:s');
                    }
                } catch (\Exception $e) {
                    throw new \Exception("字段 [{$field}] 的日期格式 '{$value}' 无效");
                }
                break;
            case 'decimal':
            case 'float':
            case 'double':
                if (!is_numeric($value)) throw new \Exception("字段 [{$field}] 的值 '{$value}' 必须为数字");
                $value = (float)$value;
                break;
        }

        // 3. 唯一性约束检查 (优化后)
        if (in_array('UNIQUE', $constraints)) {
            // 检查数据库中是否已存在
            if (isset($uniqueChecks[$field]['db']) && isset($uniqueChecks[$field]['db'][$value])) {
                throw new \Exception("字段 [{$field}] 的值 '{$value}' 已存在于数据库中");
            }
            // 检查当前文件中是否已重复
            if (isset($uniqueChecks[$field]['file']) && isset($uniqueChecks[$field]['file'][$value])) {
                throw new \Exception("字段 [{$field}] 的值 '{$value}' 在文件中重复");
            }
            // 如果通过，则将此值添加到文件缓存中，防止后续行重复
            $uniqueChecks[$field]['file'][$value] = true;
        }

        return $value;
    }

    /**
     * 预加载需要进行唯一性检查的字段的现有数据
     * @param array $fieldToConfig
     * @param string $table
     * @return array
     */
    private function prepareUniqueChecks($fieldToConfig, $table): array
    {
        $uniqueChecks = [];
        foreach ($fieldToConfig as $field => $config) {
            $constraints = $config['table_config']['field_constraint'] ?? [];
            if (in_array('UNIQUE', $constraints)) {
                // 从数据库一次性加载所有值，并以值作为键，方便快速查找
                $dbValues = Db::name($table)->whereNotNull($field)->column($field);
                $uniqueChecks[$field]['db'] = array_flip($dbValues);
                $uniqueChecks[$field]['file'] = []; // 用于存放本次导入文件中已出现的值
            }
        }
        return $uniqueChecks;
    }

    /**
     * 构建Excel列索引到数据库字段的映射
     * @param array $headerRow
     * @param array $titleToField
     * @param array $fieldToConfig
     * @return array
     * @throws ValidateException
     */
    private function buildColumnMap($headerRow, $titleToField, $fieldToConfig): array
    {
        $columnMap = [];
        foreach ($headerRow as $colIndex => $title) {
            if (empty($title)) continue; // 跳过空标题列
            if (!isset($titleToField[$title])) {
                throw new ValidateException("Excel表头中的列 '{$title}' 不是系统可识别的字段，请检查模板文件");
            }
            $field = $titleToField[$title];
            $columnMap[$colIndex] = [
                'field' => $field,
                'config' => $fieldToConfig[$field]['table_config']
            ];
        }
        if (empty($columnMap)) {
            throw new ValidateException("无法解析Excel表头，请确保第一行为标题行且与配置匹配");
        }
        return $columnMap;
    }

    /**
     * 获取并处理字段配置 (与原版保持一致)
     * @param int $menuId
     * @return array
     */
    private function getFieldConfig($menuId): array
    {
        // ... 此处代码与您提供的版本完全相同 ...
        return Db::name('dev_field')
            ->where('menu_id', $menuId)
            ->where('status', 1)
            ->whereNull('delete_time')
            ->order('sort')
            ->select()
            ->map(function ($item) {
                $config = json_decode($item['config'], true);
                if(isset($config['table_config'])){
                    $item['table_config'] = $config['table_config'];
                } else {
                    $table_config = [];
                    foreach ($config as $key => $value) {
                        if (strpos($key, 'field_') === 0) {
                            $table_config[$key] = $value;
                        }
                    }
                    $item['table_config'] = $table_config;
                }
                return $item;
            })->toArray();
    }
}
